package com.zx.oa.dao.weekResult;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.WeekResult;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WeekResultDaoImpl extends BaseDao implements WeekResultDao{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    WeekResult weekResult = null;
    @Override
    public List<WeekResult> checkWeekResult(String major,String className,String name,String date,String result,int currentPageNo,int pageSize) {
        List<WeekResult> weekResultList = new ArrayList<>();
        conn = this.getConnection();
        List<Object> parsms = new ArrayList<>();

        StringBuffer sql = new StringBuffer("SELECT r.id,s.studentName,r.examdate,r.examType,r.studentresult \n" +
                "FROM ztestudent s \n" +
                "INNER JOIN zteresult r ON r.`studentid` = s.`studentId`\n" +
                "INNER JOIN zteclassinfo c ON s.`classId` = c.`classId`\n" +
                "INNER JOIN ztemajor m ON m.`id` = c.`studyType`\n" +
                "WHERE 1=1 and examType = 2\n" );
        if (major != null && !"".equals(major)) {
            sql.append(" AND m.major = ?");
            parsms.add(major);
        }
        if (className != null && !"".equals(className)) {
            sql.append(" AND c.className like ?");
            parsms.add(className + "%");
        }
        if (name != null && !"".equals(name)) {
            sql.append(" AND s.studentName like ?");
            parsms.add(name + "%");
        }
        if (date != null && !"".equals(date)) {
            sql.append(" AND r.examdate like ?");
            parsms.add(date + "%");
        }
        // restlt 需要传一个 60 and 80 这样的字符串
        if (result != null && !"".equals(result)) {
            sql.append(" AND r.studentresult = ?");
            parsms.add(result);
        }
        sql.append(" LIMIT ?,?");
        parsms.add((currentPageNo-1)*pageSize);
        parsms.add(pageSize);


        //根据条件查询到数据
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            //依次给问号传值
            for (int i = 0; i < parsms.size(); i++) {
                pstmt.setObject(i+1,parsms.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int stuId = rs.getInt(1);
                String rName = rs.getString(2);
                Date rDate = rs.getDate(3);
                int rExamType = rs.getInt(4);
                String rc = rs.getString(5);
                WeekResult weekResult1 = new WeekResult(stuId,rName,rDate,"周考",rc);
                weekResultList.add(weekResult1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return weekResultList;

    }

    //所查询到的数据总条数
    @Override
    public int getTotalCount(String major, String className, String name, String date, String result) {

        conn = this.getConnection();
        List<Object> parsms = new ArrayList<>();

        StringBuffer sql = new StringBuffer("SELECT count(*) \n" +
                "FROM ztestudent s \n" +
                "INNER JOIN zteresult r ON r.`studentid` = s.`studentId`\n" +
                "INNER JOIN zteclassinfo c ON s.`classId` = c.`classId`\n" +
                "INNER JOIN ztemajor m ON m.`id` = c.`studyType`\n" +
                "WHERE 1=1  and examType = 2\n");
        if (major != null && !"".equals(major)) {
            sql.append(" AND m.major = ?");
            parsms.add(major);
        }
        if (className != null && !"".equals(className)) {
            sql.append(" AND c.className like ?");
            parsms.add(className + "%");
        }
        if (name != null && !"".equals(name)) {
            sql.append(" AND s.studentName like ?");
            parsms.add(name + "%");
        }
        if (date != null && !"".equals(date)) {
            sql.append(" AND r.examdate like ?");
            parsms.add(date + "%");
        }

        if (result != null && !"".equals(result)) {
            sql.append(" AND r.studentresult = ?");
            parsms.add(result);
        }
        //记录条数
        int count = 0;

        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql + "");
            //依次给win号传值
            for (int i = 0; i < parsms.size(); i++) {
                pstmt.setObject(i+1,parsms.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                count = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }


    /**
     * 修改周考成绩
     * @param date
     * @param result
     * @param stuId
     * @return
     */
    @Override
    public boolean modifyWeekResult(String date, String result ,String stuId) {

        try {
            conn = getConnection();
            String sql = "UPDATE zteresult SET examdate = ?,studentresult = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,date);
            pstmt.setString(2,result);
            pstmt.setString(3,stuId);

            int i = pstmt.executeUpdate();
            if (i == 1){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }

        return false;
    }

}
